package com;

import java.io.InputStream;
import java.util.ArrayList;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.Part;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class DataManager extends Database{

	ViolationsManager violationsManager = new ViolationsManager();
	
	public void uploadData(HttpServletRequest request, HttpServletResponse response){
		try{
			connectToDatabase();
			
			Part filePart = request.getPart("file");
			InputStream inputStream = filePart.getInputStream();
			readExcel(inputStream);
			
			// Check if there are new rule violations
			violationsManager.addViolations(-1);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
	}

	private void readExcel(InputStream inputStream){
		ArrayList<String> dataFields = new ArrayList<String>();
		try{
			Workbook w = Workbook.getWorkbook(inputStream);
			Sheet sheet = w.getSheet(0);
			
			for (int i = 1; i < sheet.getColumns(); i += 3){
				dataFields.clear();
				dataFields.add(sheet.getCell(i-1, 0).getContents());
				for (int j = 0; j < sheet.getRows(); j++){
					Cell cell = sheet.getCell(i, j);
					if (cell.getContents().equals("")) continue;
					dataFields.add(cell.getContents());
				}
				
				preparedStatement = connect.prepareStatement("REPLACE INTO `citiquantsdb`.`cities`(`Country - City`,`Year`,`Labor Availability 1`,`Labor Availability (bilingual) 1`,`Employed Bilingual Agents 1`,`Annual Attrition Rate`,`Union Presence & Activity`,`Cultural Domain Acuity Model (CDAM)`,`State or province of city`,`Tier Classification`,`Name of city`,`Country Population`,`Population Metro Area`,`Population City`,`Growth rate of city`,`Wage Inflation Rate`,`Labor cost per agent/daily`,`Supervisor`,`Attrition rates (avg reported attrition)`,`Real Estate Cost (per sq.ft/annum)`,`Telecom Cost (Monthly)`,`Teledensity Landline/dial-up`,`Teledensity Mobility`,`Internet Connectivity`,`Electricity Cost p KWh`,`Growth rate of Call Center & BPO Sector`,`service suppliers # in city`,`WTO Free Zone Extension 2015`,`CARICOM/CAFTA-DR MEMBER`,`GDP (bb)`,`GDP per capita`,`GDP real growth rate`,`unemployment`,`Employment in IT/BPO Sector`,`Inflation`,`Corporate Tax Rate %`,`Fringe Benefit %`,`Employment Rate`,`Labor Availability 2`,`Labor Availability (bilingual) 2`,`Employed Bilingual Agents 2`,`4 year university grads`,`2-yr college/technical (per year)`,`stock of graduates`,`annual business graduates`,`literacy rates`)" +
						"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);");
				
				int index = 1;
				for (String s : dataFields){
					preparedStatement.setObject(index++, s);
				}
				
				preparedStatement.executeUpdate();
			}
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public ArrayList<String[]> queryData(String query){
		ArrayList<String[]> ret = new ArrayList<String[]>();
		
		try{
			connectToDatabase();
			
			preparedStatement = connect.prepareStatement(query);
			resultSet = preparedStatement.executeQuery();
			
			int columnCount = resultSet.getMetaData().getColumnCount();
			
			while (resultSet.next()){
				String[] s = new String[columnCount];

				for (int i = 1; i <= columnCount; i++){
					s[i-1] = resultSet.getObject(i).toString();
				}
				ret.add(s);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return ret;
	}
	
}
